package cn.lena.idle.dao;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;
import java.util.zip.GZIPOutputStream;

import cn.lena.idle.pojo.Catagory;
import cn.lena.idle.pojo.DTO.CurrentCatagory;
import cn.lena.idle.pojo.Goods;
import cn.lena.idle.pojo.User;
import cn.lena.idle.util.CurrencySQL;

/**
 * @author lena
 * @date 2021/6/10
 */
public class GoodsDbHelper extends SQLiteOpenHelper {

    // 定义数据库表名
    public static final String DB_NAME = "tb_goods";
    // 创建用户信息表
    private static final String CREATE_USER_DB = "create table "+DB_NAME+" (" +
            "id integer primary key autoincrement," +
            "catagoryId integer," +
            "goodsName varchar(100)," +
            "describe varchar(200)," +
            "picture blob," +
            "price varchar(10)," +
            "phone varchar(11) default 17827414719," +
            "publishTime varchar(50)," +
            "status int default 0," +
            "buyer integer," +
            "buyerName varchar(20)," +
            "userId integer," +
            "username varchar(20)," +
            "address varchar(100))";


    /**
     * 购买商品
     * @param goods : 商品
     * @param user : 购买用户
     * @return
     */
    public int buyGoods(Goods goods, User user){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put("buyer",user.getId());
        values.put("buyerName",user.getUsername());
        values.put("status",1); //状态值为1表示已卖出
        int result=db.update(DB_NAME,values,"id = ?",new String[]{goods.getId().toString()});
        return result;
    }


    /**
     * 根据商品id列表查询商品详情
     * tip : 仅显示未销售的商品（status=0）
     * @param ids : 商品id列表
     * @return 商品详情列表
     */
    public List<Goods> listByIds(List<Integer> ids){
        if(ids.isEmpty()){
            return null;
        }
        List<Goods> lists=new ArrayList<>();
        SQLiteDatabase db=this.getWritableDatabase();
        // 先根据用户id查询出商品id ， 用商品id查出商品详情
        StringBuilder sql=new StringBuilder();
        sql.append("select * from tb_goods where status = 0 and id in (");
        sql.append(ids.get(0));
        for(Integer id:ids){
            sql.append(","+id);
        }
        sql.append(")");
        Cursor cursor=db.rawQuery(sql.toString(),null);
        while(cursor.moveToNext()){
            Goods g=new Goods();
            g.setId(cursor.getInt(cursor.getColumnIndex("id")));
            g.setCatagoryId(cursor.getInt(cursor.getColumnIndex("catagoryId")));
            g.setGoodsName(cursor.getString(cursor.getColumnIndex("goodsName")));
            g.setDescribe(cursor.getString(cursor.getColumnIndex("describe")));
            g.setPicture(cursor.getBlob(cursor.getColumnIndex("picture")));
            g.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            g.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            g.setPublishTime(cursor.getString(cursor.getColumnIndex("publishTime")));
            g.setStatus(cursor.getInt(cursor.getColumnIndex("status")));
            g.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            g.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            g.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            g.setBuyer(cursor.getInt(cursor.getColumnIndex("buyer")));
            lists.add(g);
        }
        return lists;
    }

    /**
     * 查询特定分类下的文章
     * tip:只显示未销售的
     * @param catagoryId : 分类id
     * @return
     */
    public List<Goods> getByCatagoryId(Integer catagoryId){
        List<Goods> lists=new ArrayList<>();
        SQLiteDatabase db=this.getWritableDatabase();
        String sql ="select * from tb_goods where catagoryId = ? and status = 0 order by id desc";
        Cursor cursor=db.rawQuery(sql,new String[]{catagoryId.toString()});
        while(cursor.moveToNext()){
            Goods g=new Goods();
            g.setId(cursor.getInt(cursor.getColumnIndex("id")));
            g.setCatagoryId(cursor.getInt(cursor.getColumnIndex("catagoryId")));
            g.setGoodsName(cursor.getString(cursor.getColumnIndex("goodsName")));
            g.setDescribe(cursor.getString(cursor.getColumnIndex("describe")));
            g.setPicture(cursor.getBlob(cursor.getColumnIndex("picture")));
            g.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            g.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            g.setPublishTime(cursor.getString(cursor.getColumnIndex("publishTime")));
            g.setStatus(cursor.getInt(cursor.getColumnIndex("status")));
            g.setBuyer(cursor.getInt(cursor.getColumnIndex("buyer")));
            g.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            g.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            g.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            lists.add(g);
        }
        return lists;
    }


    /**
     * 更改状态值：卖出=1，下架商品=2，售卖中=0
     */
    public int updateStatus(Integer id,Integer status){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put("status",status);
        int result=db.update(DB_NAME,values,"id = ?",new String[]{id.toString()});
        return result;
    }

    /**
     * 更新商品
     * @param goods:
     * @return
     */
    public int updateGoods(Goods goods){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put("catagoryId",goods.getCatagoryId());
        values.put("goodsName",goods.getGoodsName());
        values.put("describe",goods.getDescribe());
        values.put("picture",goods.getPicture());
        values.put("price",goods.getPrice());
        values.put("address",goods.getAddress());
        int result=db.update(DB_NAME,values,"id = ?",new String[]{goods.getId().toString()});
        return result;
    }

    /**
     * 查询所有商品
     * tip:仅查询未销售的
     * @return
     */
    public List<Goods> getAll(){
        List<Goods> lists=new ArrayList<>();
        SQLiteDatabase db=this.getWritableDatabase();
        String sql ="select * from tb_goods where status = 0 order by id desc";
        Cursor cursor=db.rawQuery(sql,null);
        while(cursor.moveToNext()){
            Goods g=new Goods();
            g.setId(cursor.getInt(cursor.getColumnIndex("id")));
            g.setCatagoryId(cursor.getInt(cursor.getColumnIndex("catagoryId")));
            g.setGoodsName(cursor.getString(cursor.getColumnIndex("goodsName")));
            g.setDescribe(cursor.getString(cursor.getColumnIndex("describe")));
            g.setPicture(cursor.getBlob(cursor.getColumnIndex("picture")));
            g.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            g.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            g.setPublishTime(cursor.getString(cursor.getColumnIndex("publishTime")));
            g.setStatus(cursor.getInt(cursor.getColumnIndex("status")));
            g.setBuyer(cursor.getInt(cursor.getColumnIndex("buyer")));
            g.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            g.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            g.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            lists.add(g);
        }
        return lists;
    }

    /**
     * 查询用户卖出或下架的商品
     * @param userId : 用户id
     * @param status : 值为1表示已卖出的，值为2表示已下架的
     * @return 商品列表
     */
    public List<Goods> getDelete(Integer userId,Integer status){
        List<Goods> lists=new ArrayList<>();
        SQLiteDatabase db=this.getWritableDatabase();
        String sql ="select * from tb_goods where userId = ? and status = ? order by id desc";
        Cursor cursor=db.rawQuery(sql,new String[]{userId.toString(),status.toString()});
        while(cursor.moveToNext()){
            Goods g=new Goods();
            g.setId(cursor.getInt(cursor.getColumnIndex("id")));
            g.setCatagoryId(cursor.getInt(cursor.getColumnIndex("catagoryId")));
            g.setGoodsName(cursor.getString(cursor.getColumnIndex("goodsName")));
            g.setDescribe(cursor.getString(cursor.getColumnIndex("describe")));
            g.setPicture(cursor.getBlob(cursor.getColumnIndex("picture")));
            g.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            g.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            g.setPublishTime(cursor.getString(cursor.getColumnIndex("publishTime")));
            g.setStatus(cursor.getInt(cursor.getColumnIndex("status")));
            g.setBuyer(cursor.getInt(cursor.getColumnIndex("buyer")));
            g.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            g.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            g.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            g.setBuyerName(cursor.getString(cursor.getColumnIndex("buyerName")));
            lists.add(g);
        }
        return lists;
    }

    /**
     * 查询用户购买记录
     * @param userId : 用户id
     * @return 商品列表
     */
    public List<Goods> listBuy(Integer userId){
        List<Goods> lists=new ArrayList<>();
        SQLiteDatabase db=this.getWritableDatabase();
        String sql ="select * from tb_goods where buyer = ? order by id desc";
        Cursor cursor=db.rawQuery(sql,new String[]{userId.toString()});
        while(cursor.moveToNext()){
            Goods g=new Goods();
            g.setId(cursor.getInt(cursor.getColumnIndex("id")));
            g.setCatagoryId(cursor.getInt(cursor.getColumnIndex("catagoryId")));
            g.setGoodsName(cursor.getString(cursor.getColumnIndex("goodsName")));
            g.setDescribe(cursor.getString(cursor.getColumnIndex("describe")));
            g.setPicture(cursor.getBlob(cursor.getColumnIndex("picture")));
            g.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            g.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            g.setPublishTime(cursor.getString(cursor.getColumnIndex("publishTime")));
            g.setStatus(cursor.getInt(cursor.getColumnIndex("status")));
            g.setBuyer(cursor.getInt(cursor.getColumnIndex("buyer")));
            g.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            g.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            g.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            lists.add(g);
        }
        return lists;
    }


    /**
     * 查询用户发布的商品 t
     * tip:包含所有状态的商品
     * @param userId : 用户id
     * @return 商品列表
     */
    public List<Goods> getPublish(Integer userId){
        List<Goods> lists=new ArrayList<>();
        SQLiteDatabase db=this.getWritableDatabase();
        String sql ="select * from tb_goods where userId = ? order by id desc";
        Cursor cursor=db.rawQuery(sql,new String[]{userId.toString()});
        while(cursor.moveToNext()){
            Goods g=new Goods();
            g.setId(cursor.getInt(cursor.getColumnIndex("id")));
            g.setCatagoryId(cursor.getInt(cursor.getColumnIndex("catagoryId")));
            g.setGoodsName(cursor.getString(cursor.getColumnIndex("goodsName")));
            g.setDescribe(cursor.getString(cursor.getColumnIndex("describe")));
            g.setPicture(cursor.getBlob(cursor.getColumnIndex("picture")));
            g.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            g.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            g.setPublishTime(cursor.getString(cursor.getColumnIndex("publishTime")));
            g.setStatus(cursor.getInt(cursor.getColumnIndex("status")));
            g.setBuyer(cursor.getInt(cursor.getColumnIndex("buyer")));
            g.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
            g.setUsername(cursor.getString(cursor.getColumnIndex("username")));
            g.setAddress(cursor.getString(cursor.getColumnIndex("address")));
            lists.add(g);
        }
        return lists;
    }


    /**
     * 添加商品 t
     * @param goods 商品
     * @return 影响行数
     */
    public int addGoods(Goods goods){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put("catagoryId",goods.getCatagoryId());
        values.put("goodsName",goods.getGoodsName());
        values.put("describe",goods.getDescribe());
        values.put("picture",goods.getPicture());
        values.put("price",goods.getPrice());
        values.put("phone",goods.getPhone());
        values.put("publishTime",goods.getPublishTime());
        values.put("status",goods.getStatus());
        values.put("userId",goods.getUserId());
        values.put("username",goods.getUsername());
        values.put("address",goods.getAddress());
        // 返回的是商品的自增id
        int result = (int) db.insert(DB_NAME, null, values);
        return result;
    }

    public GoodsDbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USER_DB);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 销毁
        db.execSQL(CurrencySQL.DROP_SQL + DB_NAME);
        // 创建
        db.execSQL(CREATE_USER_DB);
    }


}
